package com.xjk.excel;

import com.xjk.data.RandomValue;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.Random;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelTemplateFiller {

    public static void fillTemplate(List<TESTObj> list, File templateFile, String outputPath) {
        try (FileInputStream fis = new FileInputStream(templateFile);
            Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                for (Cell cell : row) {
                    String cellValue = cell.getStringCellValue();

                    if (cellValue.startsWith("{{列.单元格.")) {
                        String fieldName = cellValue.substring("{{列.单元格.".length(), cellValue.length() - 2);
                        int startRow = row.getRowNum();
                        fillColumn(sheet, startRow, cell.getColumnIndex(), list, fieldName);
                    }
                }
            }

            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
            }

        } catch (IOException | NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    private static void fillColumn(Sheet sheet, int startRow, int columnIndex, List<TESTObj> list, String field)
        throws NoSuchFieldException, IllegalAccessException {
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.getRow(startRow + i);
            if (row == null) {
                row = sheet.createRow(startRow + i);
            }
            Cell cell = row.getCell(columnIndex);
            if (cell == null) {
                cell = row.createCell(columnIndex);
            }

            TESTObj obj = list.get(i);
            Field classField = TESTObj.class.getDeclaredField(field);
            classField.setAccessible(true);
            Object value = classField.get(obj);
            if (value != null) {
                cell.setCellValue(value.toString());
            }
        }
    }

    public static void main(String[] args) {
        List<TESTObj> list = RandomTestObjGenerator.generateRandomTestObjs(1000);

        System.out.println("=============================");
        // 当前时间
        long startTime = System.currentTimeMillis();

        File templateFile = new File("D:\\desktop\\template.xlsx");
        String outputPath = "D:\\desktop\\test.xlsx";

        fillTemplate(list, templateFile, outputPath);
        // 打印耗时
        long endTime = System.currentTimeMillis();
        System.out.println("耗时：" + (endTime - startTime) + "ms");
    }


}

class TESTObj {
    String a;
    String b;
    String c;
    String d;
    String e;
    String f;
    String g;
    String h;
    String i;
    String j;
    String k;

    public TESTObj(String a, String b, String c, String d, String e, String f, String g, String h, String i, String j, String k)  {
        this.a = a;
        this.b = b;
        this.c = c;
        this.d = d;
        this.e = e;
        this.f = f;
        this.g = g;
        this.h = h;
        this.i = i;
        this.j = j;
        this.k = k;
    }
}
class RandomTestObjGenerator {

    private static final String ENGLISH_CHARS = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
    private static final String DIGITS = "0123456789";
    private static final String SPECIAL_CHARS = "#$*_-+=";
    private static final String CHINESE_CHARS = "个十百千万亿兆京垓秭穰沟涧正载极恒阿达";
    private static final String ALL_CHARS = ENGLISH_CHARS + DIGITS + SPECIAL_CHARS + CHINESE_CHARS;

    public static List<TESTObj> generateRandomTestObjs(int count) {
        List<TESTObj> list = new ArrayList<>();
        Random random = new Random();

        for (int i = 0; i < count; i++) {
            String a = generateRandomString(random);
            String b = generateRandomString(random);
            String c = generateRandomString(random);
            String d = generateRandomString(random);
            String e = generateRandomString(random);
            String f = generateRandomString(random);
            String g = generateRandomString(random);
            String h = generateRandomString(random);
            String ii = generateRandomString(random);
            String j = generateRandomString(random);
            String k = generateRandomString(random);
            list.add(new TESTObj(a, b, c, d, e, f, g, h, ii, j, k));
        }

        return list;
    }

    private static String generateRandomString(Random random) {
        int length = random.nextInt(6) + 1; // 生成1到6之间的长度
        StringBuilder sb = new StringBuilder(length);
        for (int i = 0; i < length; i++) {
            int index = random.nextInt(ALL_CHARS.length());
            sb.append(ALL_CHARS.charAt(index));
        }
        return sb.toString();
    }
}
class Fill{
    public static void a(List<TESTObj> list, int i, Cell cell) {
        cell.setCellValue(list.get(i).a);
    }
}


class Test{

    public static void main(String[] args) throws IOException, InterruptedException {

        Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
        Sheet sheet = wb.createSheet();

        excelHeaderSettings(wb, sheet,"#f40",AObj.class);

        List<AObj> o = new ArrayList<AObj>(){{
            for (int i = 0; i < 10000; i++) {
                BigDecimal bigDecimal = RandomValue.randomBigDecimal("-10", "20", 2);
                add(new AObj("aaaaaaaaaaaaaaaaaaaaaaa","bbbbbbbbbbbbbbbbbbbbbbbbbb","ccccccccccccc","ddddddddddddddddddd",i,LocalDateTime.now(),bigDecimal));
            }
        }};

        int i = 1;
        for (AObj aObj : o) {
            int index = o.indexOf(aObj)+i;
            excelValueSetting(wb,sheet,index,"#fff",aObj);
            System.out.println("第"+index+"行写入完成");
        }



        Row row = sheet.createRow(2);
        row.setHeightInPoints(30);
        createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM,"你好");
        createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM,"你好");
        createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER,"你好");
        createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER,"你好");
        createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY,"你好");
        createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP,"你好");
        createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP,"你好");


        row = sheet.createRow(4);

        createCellBottomBorder(wb, row,0, BorderStyle.THIN, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,1, BorderStyle.MEDIUM, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,2, BorderStyle.DASHED, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,3, BorderStyle.DOTTED, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,4, BorderStyle.THICK, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,5, BorderStyle.DOUBLE, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,6, BorderStyle.HAIR, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,7, BorderStyle.MEDIUM_DASHED, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,8, BorderStyle.DASH_DOT, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,9, BorderStyle.MEDIUM_DASH_DOT, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,10, BorderStyle.DASH_DOT_DOT, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,11, BorderStyle.MEDIUM_DASH_DOT_DOT, IndexedColors.BLACK,"你好");
        createCellBottomBorder(wb, row,12, BorderStyle.SLANTED_DASH_DOT, IndexedColors.BLACK,"你好");

        row = sheet.createRow(6);
        createCellBorder(wb, row,0, BorderStyle.THIN, IndexedColors.BLACK1,"你好");
        createCellBorder(wb, row,1, BorderStyle.THIN, IndexedColors.WHITE1,"你好");
        createCellBorder(wb, row,2, BorderStyle.THIN, IndexedColors.RED1,"你好");
        createCellBorder(wb, row,3, BorderStyle.THIN, IndexedColors.BRIGHT_GREEN1,"你好");
        createCellBorder(wb, row,4, BorderStyle.THIN, IndexedColors.BLUE1,"你好");
        createCellBorder(wb, row,5, BorderStyle.THIN, IndexedColors.YELLOW1,"你好");
        createCellBorder(wb, row,6, BorderStyle.THIN, IndexedColors.PINK1,"你好");
        createCellBorder(wb, row,7, BorderStyle.THIN, IndexedColors.TURQUOISE1,"你好");
        createCellBorder(wb, row,8, BorderStyle.THIN, IndexedColors.BLACK,"你好");
        createCellBorder(wb, row,9, BorderStyle.THIN, IndexedColors.WHITE,"你好");
        row = sheet.createRow(8);
        createCellBorder(wb, row,0, BorderStyle.THIN, IndexedColors.RED,"你好");
        createCellBorder(wb, row,1, BorderStyle.THIN, IndexedColors.BRIGHT_GREEN,"你好");
        createCellBorder(wb, row,2, BorderStyle.THIN, IndexedColors.BLUE,"你好");
        createCellBorder(wb, row,3, BorderStyle.THIN, IndexedColors.YELLOW,"你好");
        createCellBorder(wb, row,4, BorderStyle.THIN, IndexedColors.PINK,"你好");
        createCellBorder(wb, row,5, BorderStyle.THIN, IndexedColors.TURQUOISE,"你好");
        createCellBorder(wb, row,6, BorderStyle.THIN, IndexedColors.DARK_RED,"你好");
        createCellBorder(wb, row,7, BorderStyle.THIN, IndexedColors.GREEN,"你好");
        createCellBorder(wb, row,8, BorderStyle.THIN, IndexedColors.DARK_BLUE,"你好");
        createCellBorder(wb, row,9, BorderStyle.THIN, IndexedColors.DARK_YELLOW,"你好");

        row = sheet.createRow(10);
        createCellBorder(wb, row,0, BorderStyle.THIN, IndexedColors.VIOLET,"你好");
        createCellBorder(wb, row,1, BorderStyle.THIN, IndexedColors.TEAL,"你好");
        createCellBorder(wb, row,2, BorderStyle.THIN, IndexedColors.GREY_25_PERCENT,"你好");
        createCellBorder(wb, row,3, BorderStyle.THIN, IndexedColors.GREY_50_PERCENT,"你好");
        createCellBorder(wb, row,4, BorderStyle.THIN, IndexedColors.CORNFLOWER_BLUE,"你好");
        createCellBorder(wb, row,5, BorderStyle.THIN, IndexedColors.MAROON,"你好");
        createCellBorder(wb, row,6, BorderStyle.THIN, IndexedColors.LEMON_CHIFFON,"你好");
        createCellBorder(wb, row,7, BorderStyle.THIN, IndexedColors.LIGHT_TURQUOISE1,"你好");
        createCellBorder(wb, row,8, BorderStyle.THIN, IndexedColors.ORCHID,"你好");
        createCellBorder(wb, row,9, BorderStyle.THIN, IndexedColors.CORAL,"你好");

        row = sheet.createRow(12);
        createCellBorder(wb, row,0, BorderStyle.THIN, IndexedColors.ROYAL_BLUE,"你好");
        createCellBorder(wb, row,1, BorderStyle.THIN, IndexedColors.LIGHT_CORNFLOWER_BLUE,"你好");
        createCellBorder(wb, row,2, BorderStyle.THIN, IndexedColors.SKY_BLUE,"你好");
        createCellBorder(wb, row,3, BorderStyle.THIN, IndexedColors.LIGHT_TURQUOISE,"你好");
        createCellBorder(wb, row,4, BorderStyle.THIN, IndexedColors.LIGHT_GREEN,"你好");
        createCellBorder(wb, row,5, BorderStyle.THIN, IndexedColors.LIGHT_YELLOW,"你好");
        createCellBorder(wb, row,6, BorderStyle.THIN, IndexedColors.PALE_BLUE,"你好");
        createCellBorder(wb, row,7, BorderStyle.THIN, IndexedColors.ROSE,"你好");
        createCellBorder(wb, row,8, BorderStyle.THIN, IndexedColors.LAVENDER,"你好");
        createCellBorder(wb, row,9, BorderStyle.THIN, IndexedColors.TAN,"你好");

        row = sheet.createRow(14);
        createCellBorder(wb, row,0, BorderStyle.THIN, IndexedColors.LIGHT_BLUE,"你好");
        createCellBorder(wb, row,1, BorderStyle.THIN, IndexedColors.AQUA,"你好");
        createCellBorder(wb, row,2, BorderStyle.THIN, IndexedColors.LIME,"你好");
        createCellBorder(wb, row,3, BorderStyle.THIN, IndexedColors.GOLD,"你好");
        createCellBorder(wb, row,4, BorderStyle.THIN, IndexedColors.LIGHT_ORANGE,"你好");
        createCellBorder(wb, row,5, BorderStyle.THIN, IndexedColors.ORANGE,"你好");
        createCellBorder(wb, row,6, BorderStyle.THIN, IndexedColors.BLUE_GREY,"你好");
        createCellBorder(wb, row,7, BorderStyle.THIN, IndexedColors.GREY_40_PERCENT,"你好");
        createCellBorder(wb, row,8, BorderStyle.THIN, IndexedColors.DARK_TEAL,"你好");
        createCellBorder(wb, row,9, BorderStyle.THIN, IndexedColors.SEA_GREEN,"你好");

        row = sheet.createRow(16);
        createCellBorder(wb, row,0, BorderStyle.THIN, IndexedColors.DARK_GREEN,"你好");
        createCellBorder(wb, row,1, BorderStyle.THIN, IndexedColors.OLIVE_GREEN,"你好");
        createCellBorder(wb, row,2, BorderStyle.THIN, IndexedColors.BROWN,"你好");
        createCellBorder(wb, row,3, BorderStyle.THIN, IndexedColors.PLUM,"你好");
        createCellBorder(wb, row,4, BorderStyle.THIN, IndexedColors.INDIGO,"你好");
        createCellBorder(wb, row,5, BorderStyle.THIN, IndexedColors.GREY_80_PERCENT,"你好");
        createCellBorder(wb, row,6, BorderStyle.THIN, IndexedColors.AUTOMATIC,"你好");

        row = sheet.createRow(20);
        createAllCell(wb, row, 0,
            HorizontalAlignment.CENTER,
            VerticalAlignment.CENTER,
            BorderStyle.DOUBLE,
            IndexedColors.LIGHT_TURQUOISE,
            "楷书",
            "#4f3",
            "你好");





        // Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("D:\\desktop\\xssf-align.xlsx")) {
            wb.write(fileOut);
        }
        wb.close();


    }

    public static void excelHeaderSettings(Workbook wb,Sheet sheet, String hexColor, Class clazz){

        Row row = sheet.createRow(0);
        Field[] declaredFields = clazz.getDeclaredFields();
        for (int i = 0,cellIndex=0; i < declaredFields.length; i++,cellIndex++) {
            Field field = declaredFields[i];
            field.setAccessible(true);
            ExcelField annotation = field.getAnnotation(ExcelField.class);
            ExcelFiledStyle annotation1 = field.getAnnotation(ExcelFiledStyle.class);
            String name = Objects.isNull(annotation)?field.getName():annotation.name();
            if(Objects.nonNull(annotation)&&annotation.ignore()){
                cellIndex--;
                continue;
            }
            String fontFamily = "微软雅黑";
            if(Objects.nonNull(annotation1)){
                sheet.setColumnWidth(cellIndex, annotation1.width()*256);
                fontFamily = annotation1.fontFamily();
            }
            createAllCell(wb, row, cellIndex,
                HorizontalAlignment.CENTER,
                VerticalAlignment.CENTER,
                BorderStyle.DOUBLE,
                IndexedColors.LIGHT_TURQUOISE,
                fontFamily,
                hexColor,
                name);
        }

    }

    public static void excelValueSetting(Workbook wb,Sheet sheet,Integer rowIndex, String hexColor, Object obj){
        Class<?> clazz = obj.getClass();

        Row row = sheet.createRow(rowIndex);
        Field[] declaredFields = clazz.getDeclaredFields();
        for (int i = 0,cellIndex=0; i < declaredFields.length; i++,cellIndex++) {
            Field field = declaredFields[i];
            field.setAccessible(true);

            ExcelField annotation = field.getAnnotation(ExcelField.class);
            ExcelFiledStyle annotation1 = field.getAnnotation(ExcelFiledStyle.class);
            if(Objects.nonNull(annotation)&&annotation.ignore()){
                cellIndex--;
                continue;
            }
            String fontFamily = "微软雅黑";
            if(Objects.nonNull(annotation1)){
                fontFamily = annotation1.fontFamily();
            }

            Object value = null;
            try {
                if (field.getType() == BigDecimal.class) {
                    value = ((BigDecimal)field.get(obj)).toPlainString();
                }else {
                    value = field.get(obj);
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

            if (value == null) {
                value = "";
            }

            if (isDateType(field.getType())) {
                String dateFormat = Objects.isNull(annotation1)?"yyyy-MM-dd HH:mm:ss":annotation1.dateFormat();
                Date date = convertToDate(value);
                createAllCell(wb, row, cellIndex,
                    HorizontalAlignment.CENTER,
                    VerticalAlignment.CENTER,
                    BorderStyle.DOUBLE,
                    IndexedColors.LIGHT_TURQUOISE,
                    hexColor,
                    dateFormat,
                    date);
            }else {
                createAllCell(wb, row, cellIndex,
                    HorizontalAlignment.CENTER,
                    VerticalAlignment.CENTER,
                    BorderStyle.DOUBLE,
                    IndexedColors.LIGHT_TURQUOISE,
                    fontFamily,
                    hexColor,
                    value.toString());
            }
        }

    }



    private static void createCellBottomBorder(Workbook wb, Row row, int column,BorderStyle borderStyle, IndexedColors color,String value)  {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(borderStyle);
        style.setBottomBorderColor(color.getIndex());
        cell.setCellStyle(style);
    }

    private static void createCellLeftBorder(Workbook wb, Row row, int column,BorderStyle borderStyle, IndexedColors color,String value)  {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle style = wb.createCellStyle();
        style.setBorderLeft(borderStyle);
        style.setLeftBorderColor(color.getIndex());
        cell.setCellStyle(style);
    }

    private static void createCellRightBorder(Workbook wb, Row row, int column,BorderStyle borderStyle, IndexedColors color,String value)  {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(borderStyle);
        style.setRightBorderColor(color.getIndex());
        cell.setCellStyle(style);
    }

    private static void createCellTopBorder(Workbook wb, Row row, int column,BorderStyle borderStyle, IndexedColors color,String value)  {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle style = wb.createCellStyle();
        style.setBorderTop(borderStyle);
        style.setTopBorderColor(color.getIndex());
        cell.setCellStyle(style);
    }

    private static void createCellBorder(Workbook wb, Row row, int column,BorderStyle borderStyle, IndexedColors color,String value)  {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(borderStyle);
        style.setBottomBorderColor(color.getIndex());
        style.setBorderLeft(borderStyle);
        style.setLeftBorderColor(color.getIndex());
        style.setBorderRight(borderStyle);
        style.setRightBorderColor(color.getIndex());
        style.setBorderTop(borderStyle);
        style.setTopBorderColor(color.getIndex());
        cell.setCellStyle(style);
    }

    private static void createAllCell(Workbook wb, Row row, int column,
        HorizontalAlignment halign,
        VerticalAlignment valign,
        BorderStyle borderStyle,
        IndexedColors borderColor,
        IndexedColors fillColor,
        String value) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cellStyle.setBorderBottom(borderStyle);
        cellStyle.setBottomBorderColor(borderColor.getIndex());
        cellStyle.setBorderLeft(borderStyle);
        cellStyle.setLeftBorderColor(borderColor.getIndex());
        cellStyle.setBorderRight(borderStyle);
        cellStyle.setRightBorderColor(borderColor.getIndex());
        cellStyle.setBorderTop(borderStyle);
        cellStyle.setTopBorderColor(borderColor.getIndex());

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(fillColor.getIndex());
        cell.setCellStyle(cellStyle);
    }

    public static byte[] hexToRgb(String hex) {
        // 去掉前导的 '#'，如果有的话
        hex = hex.replace("#", "");

        if (hex.length() == 3){
            hex = "" + hex.charAt(0) + hex.charAt(0)
                + hex.charAt(1) + hex.charAt(1)
                + hex.charAt(2) + hex.charAt(2);
        }

        // 将 HEX 转换为整数
        byte r = (byte) Integer.parseInt(hex.substring(0, 2), 16);
        byte g = (byte) Integer.parseInt(hex.substring(2, 4), 16);
        byte b = (byte) Integer.parseInt(hex.substring(4, 6), 16);
        return new byte[]{r, g, b};
    }


    private static boolean isDateType(Class<?> clazz) {
        return clazz.equals(Date.class) ||
            clazz.equals(LocalDate.class) ||
            clazz.equals(LocalDateTime.class) ||
            clazz.equals(Instant.class) ||
            clazz.equals(java.sql.Date.class) ||
            clazz.equals(java.sql.Timestamp.class);
    }

    private static Date convertToDate(Object value) {
        if (value instanceof Date) {
            return (Date) value;
        } else if (value instanceof LocalDate) {
            return Date.from(((LocalDate) value).atStartOfDay(ZoneId.systemDefault()).toInstant());
        } else if (value instanceof LocalDateTime) {
            return Date.from(((LocalDateTime) value).atZone(ZoneId.systemDefault()).toInstant());
        } else if (value instanceof Instant) {
            return Date.from((Instant) value);
        } else if (value instanceof java.sql.Date) {
            return new Date(((java.sql.Date) value).getTime());
        } else if (value instanceof java.sql.Timestamp) {
            return new Date(((java.sql.Timestamp) value).getTime());
        } else {
            throw new IllegalArgumentException("Unsupported date type: " + value.getClass());
        }
    }


    private static void createAllCell(Workbook wb, Row row, int column,
        HorizontalAlignment halign,
        VerticalAlignment valign,
        BorderStyle borderStyle,
        IndexedColors borderColor,
        String fontFamily,
        String hex,
        String value) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cellStyle.setBorderBottom(borderStyle);
        cellStyle.setBottomBorderColor(borderColor.getIndex());
        cellStyle.setBorderLeft(borderStyle);
        cellStyle.setLeftBorderColor(borderColor.getIndex());
        cellStyle.setBorderRight(borderStyle);
        cellStyle.setRightBorderColor(borderColor.getIndex());
        cellStyle.setBorderTop(borderStyle);
        cellStyle.setTopBorderColor(borderColor.getIndex());

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Font font = wb.createFont();
        font.setFontName(fontFamily);

        cellStyle.setFont(font);
        XSSFColor xssfColor = new XSSFColor(hexToRgb(hex), null);
        cellStyle.setFillForegroundColor(xssfColor);
        cell.setCellStyle(cellStyle);
    }

    private static void createAllCell(Workbook wb, Row row, int column,
        HorizontalAlignment halign,
        VerticalAlignment valign,
        BorderStyle borderStyle,
        IndexedColors borderColor,
        String hex,
        String dataFormat,
        Date dateValue) {
        Cell cell = row.createCell(column);
        cell.setCellValue(dateValue);
        XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cellStyle.setBorderBottom(borderStyle);
        cellStyle.setBottomBorderColor(borderColor.getIndex());
        cellStyle.setBorderLeft(borderStyle);
        cellStyle.setLeftBorderColor(borderColor.getIndex());
        cellStyle.setBorderRight(borderStyle);
        cellStyle.setRightBorderColor(borderColor.getIndex());
        cellStyle.setBorderTop(borderStyle);
        cellStyle.setTopBorderColor(borderColor.getIndex());

        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(dataFormat));


        XSSFColor xssfColor = new XSSFColor(hexToRgb(hex), null);
        cellStyle.setFillForegroundColor(xssfColor);
        cell.setCellStyle(cellStyle);
    }

    private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign,String value) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
    }
}

class AObj{

    @ExcelField(name ="A")
    String a;
    @ExcelField(name ="B")
    String b;
    @ExcelField(name ="C")
    String c;
    @ExcelField(ignore = true)
    String d;
    Integer x;
    @ExcelField(name ="日期")
    @ExcelFiledStyle(fontColor = "FF0000",width = 30,fontFamily = "华文楷体")
    LocalDateTime e;
    BigDecimal f;

    public AObj(String a, String b, String c, String d, Integer x, LocalDateTime e, BigDecimal f)   {
        this.a = a;
        this.b = b;
        this.c = c;
        this.d = d;
        this.e = e;
        this.x = x;
        this.f = f;
    }
}




































































































































































































































































































































